<?php
    require_once '../../common/DBHelper.php';
    $currentPage = $_POST['currentPage'];
    $pageSize = $_POST['pageSize'];
    $category_id = $_POST['category_id'];

    // 连接数据库
    $conn = conn();
    $offset = ($currentPage - 1) * $pageSize;
$sql = "SELECT p.id,p.title,u.nickname,p.created,p.views,(SELECT count(*) FROM comments WHERE id = p.id) as commentCount ,p.likes,c.`name`,p.feature FROM posts p LEFT JOIN users u on p.user_id = u.id LEFT JOIN categories c on p.catepory_id = c.id WHERE p.category_id = {$category_id} LIMIT {$offset},{$pageSize}";

    // 执行查询
    $res = query($conn, $sql);
    // 还要求出一个最大页码数
    // 最大页码数 = ceil(数据总数 / 每页显示的数量)

    $sqlCt = "SELECT count(*) as total FROM posts WHERE category_id = {$category_id}";
    $connRes = query($conn, $sqlCt);
    $total = $connRes[0]['total'];
    $pageCount = ceil($total / $pageSize);

    $response = ['code' => 0,'msg' => '操作失败'];
    
    if ($res) {
        $response['code'] = 1;
        $response['msg'] = '操作成功';
        $response['data'] = $res;
        $response['pageCount'] = $pageCount;
    }
    // 返回给前端
    echo json_encode($response, JSON_UNESCAPED_UNICODE);
